<?php
class Data_selectController extends Controller{
	public function actionSelectDataAjxResult(){
		/*
		 * obj    : model object
		* filter : {"is_active":1,"tingkatan_id":$("#tingkatan_id").val()};
		* value_field     : id (default)
		* title_field     : name (default)
		* untuk field yang mau di ambil dari relation parentnya menggunakana titik, exp: kelas.name
		*/
		//$obj = "MTahunAjaran";
		$obj = Yii::app()->request->getParam("obj","");
		$filter = Yii::app()->request->getParam("filter",array());
		$value_field = Yii::app()->request->getParam("value_field","id");
		$title_field = Yii::app()->request->getParam("title_field","name");
		$group = Yii::app()->request->getParam("group","");
		$join = Yii::app()->request->getParam("join","");
		
		
		$criteria = new CDbCriteria();
		foreach ($filter as $key => $value){
			$criteria->addColumnCondition(array($key => $value));
		}
		$criteria->group=$group;
		$criteria->join=$join;
		$rw = $obj::model()->findAll($criteria);
			
		$result = array();
	
		for ($i =0; $i < sizeof($rw) ;$i++){
			$row = array();
	
			$arrTmp = explode(".", $value_field);			
			$arrData = $rw[$i];
			foreach($arrTmp as $tmp){
				$arrData = $arrData[$tmp];
			}
			$row["value"] = $arrData;
	
			$arrTmp = explode(".", $title_field);
			$arrData = $rw[$i];
			foreach($arrTmp as $tmp){
				$arrData = $arrData[$tmp];
			}
			
			$row["title"] = $arrData;
			$result[] = $row;
		}
		$this->returnJSON($result);		
	}
	public function actionSelectKotaAjxResult(){
		$propinsi = Yii::app()->request->getParam("propinsi","");
		$arrKota = array();
		$arrKota[] = array("value"=>"","title"=>"");
		
		$listKota = SysArea::model()->findAll(array('select'=>'kabupaten', 'distinct'=>true,'condition'=>"propinsi =:propinsi","params"=>array(":propinsi"=>$propinsi)));
		foreach ($listKota as $row){
			$arrKota[] = array("value"=>$row->kabupaten,"title"=>$row->kabupaten);
		}
		$this->returnJSON($arrKota);			
	}
	public function actionSelectAreaAjxResult(){
		$propinsi = Yii::app()->request->getParam("propinsi","");
		$kota = Yii::app()->request->getParam("kota","");
		$arrArea = array();
		$arrArea[] = array("value"=>"","title"=>"");	
		$listArea = SysArea::model()->findAll(array('select'=>'kecamatan', 'distinct'=>true,'condition'=>"propinsi =:propinsi and kabupaten =:kota ","params"=>array(":propinsi"=>$propinsi,":kota"=>$kota)));
		foreach ($listArea as $row){
			$arrArea[] = array("value"=>$row->kecamatan,"title"=>$row->kecamatan);
		}
		$this->returnJSON($arrArea);
	}
	public function actionSelectActiveTingkatanByPeriodAndDepartment(){
		$period = Yii::app()->request->getParam("period","");
		$department_id = Yii::app()->request->getParam("department_id","");
		$startDate = Utils::getPeriodStartDate($period);
		
		$ssql = "select distinct d.id as id,d.name as name from m_student_class a 
				inner join m_kelas_tahun_ajaran b on a.kelas_tahun_ajaran_id = b.id  
				inner join m_kelas c on b.kelas_id = c.id
				inner join m_tingkatan d on c.tingkatan_id = d.id 
				inner join m_tahun_ajaran e on b.tahun_ajaran_id = e.id
				where
				d.department_id =:deptId and
				e.start_date <= :startDate and e.end_date >= :startDate";
		$conn = Yii::app()->db;
		$cmd = $conn->createCommand($ssql);
		$arrResult = $cmd->query(array(":deptId"=>$department_id,":startDate"=>$startDate));
		$result = array();
		
		foreach ($arrResult as $row){
			
			$result[] = array("value"=>$row["id"],"title"=>$row["name"]);
		}
		$this->returnJSON($result);
	}
	
	public function actionSelectActiveKelasTahunAjaranByPeriodAndTingkatan(){
		$period = Yii::app()->request->getParam("period","");
		$tingkatan_id = Yii::app()->request->getParam("tingkatan_id","");
		$startDate = Utils::getPeriodStartDate($period);
	
		$ssql = "select distinct b.id as id,c.name as name from m_student_class a
				inner join m_kelas_tahun_ajaran b on a.kelas_tahun_ajaran_id = b.id
				inner join m_kelas c on b.kelas_id = c.id
				inner join m_tahun_ajaran e on b.tahun_ajaran_id = e.id
				where
				c.id =:tingkatanId and
				e.start_date <= :startDate and e.end_date >= :startDate";
		$conn = Yii::app()->db;
		$cmd = $conn->createCommand($ssql);
		$arrResult = $cmd->query(array(":tingkatanId"=>$tingkatan_id,":startDate"=>$startDate));
		$result = array();
	
		foreach ($arrResult as $row){				
			$result[] = array("value"=>$row["id"],"title"=>$row["name"]);
		}
		$this->returnJSON($result);
	}

	public function actionSelectActiveTingkatanByDepartment(){
		$department_id = Yii::app()->request->getParam("department_id","");		
		$tahunAjaran = CMTahunAjaran::getTahunAjaranActive($department_id);		
		$tahunAjaranId = $tahunAjaran == null ? 0: $tahunAjaran->id;		
		$ssql ="select distinct b.tingkatan_id as id,c.name from m_kelas_tahun_ajaran a
					inner join m_kelas b on a.kelas_id = b.id
					inner join m_tingkatan c on b.tingkatan_id = c.id
					where a.is_active = 1 and b.is_active = 1 and c.is_active = 1 and a.tahun_ajaran_id = $tahunAjaranId";
		$conn = Yii::app()->db;
		$cmd = $conn->createCommand($ssql);
		$arrResult = $cmd->queryAll();
		$result = array();
		foreach ($arrResult as $row){
				
			$result[] = array("value"=>$row["id"],"title"=>$row["name"]);
		}
		$this->returnJSON($result);
	}
	
	public function actionSelectActiveKelasByTingkatan(){
		$tingkatan_id = Yii::app()->request->getParam("tingkatan_id",0);
		$tingkatan_id = $tingkatan_id == ""?0:$tingkatan_id;
		$ssql ="select distinct b.id as id,b.name from m_kelas_tahun_ajaran a
		inner join m_kelas b on a.kelas_id = b.id
		inner join m_tingkatan c on b.tingkatan_id = c.id
		where a.is_active = 1 and b.is_active = 1 and c.is_active = 1 and b.tingkatan_id = $tingkatan_id";
		$conn = Yii::app()->db;
		$cmd = $conn->createCommand($ssql);
		$arrResult = $cmd->queryAll();
		$result = array();
		foreach ($arrResult as $row){
			$result[] = array("value"=>$row["id"],"title"=>$row["name"]);
		}
		$this->returnJSON($result);
	}
}
?>